﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using NLBS.Entity;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace NLBS.Data
{
    public partial class SqlDataProvider : DbProvider
    {
        #region Article

        public override IPagedCollection<Article> GetRssArticleByPage(int pageSize)
        {
           
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T)
            And log_enablerss=1  order by log_isTop desc,log_postTime desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_Article where log_enablerss=1", pageSize, 10);


            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);
                }

                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                return lists;
            }
        }

        public override IPagedCollection<Article> GetAllArticleByPage(int startIndex, int pageSize)
        {
            StringBuilder sb=new StringBuilder();
            sb.AppendFormat(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T)", pageSize, startIndex);
            if (startIndex == 1)
            {
                sb.Append(" order by log_isTop desc,log_postTime desc;");
            }
            else
            {
                sb.Append(" order by log_postTime desc;");
            }
            sb.Append("SELECT COUNT(log_id) AS TotalRecords from blog_Article");
            
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = this.ExecuteReader(sb.ToString()))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);
                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                return lists;
            }
        }

        public DbParameter CreateParameter(string paramsName,DbType dbtype,object objvalue)
        {
            SqlParameter objParam = new SqlParameter("@"+paramsName, objvalue);
            objParam.DbType = dbtype;
            return objParam;
        }

        public override IPagedCollection<Article> GetCatArticleByPage(int startIndex, int pageSize, int catID)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T) and log_catID=@catID  order by log_id desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE log_catID=@catID", pageSize, startIndex);

            List<DbParameter> objParams = new List<DbParameter>();
            objParams.Add(CreateParameter("catID", DbType.Int32, catID));
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = this.ExecuteReader(strPageSQL, objParams.ToArray()))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);
                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                return lists;
            }

        }

        public override IPagedCollection<Article> GetSelectedArticleByPage(int startIndex, int pageSize)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T) and log_selected=1 order by log_postTime desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_Article where log_selected=1", pageSize, startIndex);

            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL);


            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);
                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                return lists;
            }

        }

        public override IList<Article> GetTopArticle(int intCount)
        {
            
            string sqlWHERE = string.Empty;
            if (intCount < 10)
            {
                intCount = 10;
            }
            foreach (Category item in this.GetCategory())
            {
                if (item.Hidden) sqlWHERE += " AND log_catid<>" + item.CategoryID;
            }
            List<string> intList = new List<string>();
            DbCommand dbHiddenCommand = db.GetSqlStringCommand("SELECT TOP " + intCount + " log_id FROM [blog_Article] WHERE log_mode=1 " + sqlWHERE + " ORDER BY log_postTime DESC");
            using (IDataReader dataReader = db.ExecuteReader(dbHiddenCommand))
            {
                while (dataReader.Read())
                {
                    //if (!intList.Contains(Convert.ToInt32(dataReader["log_id"])))
                        intList.Add(Convert.ToString(dataReader["log_id"]));
                }
            }
            DbCommand dbAllCommand = db.GetSqlStringCommand("SELECT TOP " + intCount + " log_id FROM [blog_Article] ORDER BY log_postTime DESC");
            using (IDataReader dataReader = db.ExecuteReader(dbAllCommand))
            {
                while (dataReader.Read())
                {
                    //if (!intList.Contains(Convert.ToInt32(dataReader["log_id"])))
                    intList.Add(Convert.ToString(dataReader["log_id"]));
                }
            }
            IList<Article> lists = new List<Article>();
            if (intList.Count == 0)
            {
                return lists;
            }
           
            intList.Sort();
            string arrItem = intList[0];
            for (int i = 1; i < intList.Count; i++)
            {
                if (arrItem == intList[i])
                {
                    intList.RemoveAt(i);
                    i--;
                }
                else
                {
                    arrItem = intList[i];
                }
            }
            
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT blog_Article.*,blog_Category.* FROM 
            blog_Article INNER JOIN blog_Category ON 
            blog_Article.log_catID = blog_Category.cat_id 
            and blog_Article.log_id in ({0})  ORDER BY blog_Article.log_postTime DESC", String.Join(",", intList.ToArray())));
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateNoCatArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        public override IList<Article> GetArticle()
        {
            
            DbCommand dbCommand = db.GetStoredProcCommand("GetAllArticle");
            IList<Article> lists = new List<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateNoCatArticleFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        public override IPagedCollection<Article> GetArticleByUserID(int startIndex, int pageSize, int userID)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T) and log_authorID=@userID  order by log_id desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE log_authorID=@userID", pageSize, startIndex);

            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL);
            db.AddInParameter(dbCommand, "userID", DbType.Int32, userID);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);
                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                return lists;
            }
            //SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE log_authorID=@userID
            
        }

        public override IPagedCollection<Article> GetPagedArticleByMonth(int startIndex, int pageSize, int year, int month)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article WHERE  Year(log_postTime) = @year
	        AND Month(log_postTime) = @month order by log_id desc) as T) 
            AND  Year(log_postTime) = @year AND Month(log_postTime) = @month order by log_postTime desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE  Year(log_postTime) = @year
	        AND Month(log_postTime) = @month", pageSize, startIndex);


            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL);
            db.AddInParameter(dbCommand, "year", DbType.Int32, year);
            db.AddInParameter(dbCommand, "month", DbType.Int32, month);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);
                    lists.Add(article);
                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
            }
            return lists;
        }

        public override IPagedCollection<Article> GetPagedArticleByYear(int startIndex, int pageSize, int year)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article where Year(log_postTime) = @year order by log_id desc) as T) 
            and  Year(log_postTime) = @year order by log_postTime desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE Year(log_postTime) = @year", pageSize, startIndex);


            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL);
            db.AddInParameter(dbCommand, "year", DbType.Int32, year);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);
                    lists.Add(article);
                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
            }
            return lists;
        }

        public override IPagedCollection<Article> GetPagedArticleByDay(int startIndex, int pageSize, DateTime date)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T) 
            and  Year(log_postTime) = Year(@Date) 
	        AND Month(log_postTime) = Month(@Date)
            AND Day(log_postTime) = Day(@Date) order by log_postTime desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE  Year(log_postTime) = Year(@Date) 
	        AND Month(log_postTime) = Month(@Date)
            AND Day(log_postTime) = Day(@Date)", pageSize, startIndex);

            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL);
            db.AddInParameter(dbCommand, "date", DbType.DateTime, date);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);
                    lists.Add(article);
                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
            }
            return lists;
        }

        public override IPagedCollection<Article> GetPagedArticleBySearch(int startIndex, int pageSize, string prefix)
        {
            StringBuilder sb = new StringBuilder();
            string[] prefixArray = prefix.Split(new char[] { ' ' });
            for (int i = 0; i < prefixArray.Length; i++)
            {
                if (i > 0)
                {
                    sb.Append("AND ");
                }
                sb.AppendFormat("log_title LIKE '%{0}%'", prefixArray[i]);
                // OR log_content0 LIKE '%"+func.checkStr(arrKeywords[i])+"%' OR log_content1 LIKE '%"+func.checkStr(arrKeywords[i])+"%')
                sb.AppendFormat(" OR log_content0 LIKE '%{0}%'", prefixArray[i]);
                sb.AppendFormat(" OR log_content1 LIKE '%{0}%'", prefixArray[i]);
            }

            //string sqllink=
            string strPageSQL2 = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T) AND {2} order by log_id desc;
            SELECT COUNT(log_id) AS TotalRecords from blog_Article where {2}", pageSize, startIndex, sb.ToString());

            
            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL2);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateTopAndCommentArticleFromIDataReader(dataReader);
                    //article.Category = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                    lists.Add(article);

                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                return lists;
            }
        }

        public override Article GetArticleByID(int articleID)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT   cat.*,cat.cat_name as cat_Name,log_id, log_catID, log_title, log_authorID, log_author, log_editMark, log_trackbackURL, log_content0, log_content1, 
                log_mode, log_locked, log_selected, log_ubbFlags, log_postTime, log_ip, log_commentCount, log_viewCount, 
                log_trackbackCount,log_istop,log_enablerss
           FROM      blog_Article Article left join blog_category cat on Article.log_catid=cat.cat_id
           WHERE   (log_id = @articleID)");
            db.AddInParameter(dbCommand, "articleID", DbType.Int32, articleID);
            IDataReader dr = db.ExecuteReader(dbCommand);
            Article article = null;
            if (dr.Read())
            {
                article = CommonDataProvider.PopulateTopAndCommentArticleFromIDataReader(dr);
            }
            article.Tags = this.GetTagByID(article);
            return article;
        }

        public override int InsertArticle(Article item)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"INSERT INTO [dbo].[blog_Article] 
            ([log_catID], [log_title], [log_authorID], [log_author], [log_editMark],
             [log_trackbackURL], [log_content0], [log_content1], [log_mode], [log_locked],
              [log_selected], [log_ubbFlags], [log_postTime], [log_ip], 
              [log_commentCount], [log_viewCount], [log_trackbackCount],log_istop,log_enablerss) 
              VALUES 
              (@catID, @title, @authorID, @author, @editMark, 
              @trackbackURL, @content0, @content1, @mode, @locked, 
              @selected, @ubbFlags, @postTime, @ip, 
              @commentCount, @viewCount, @trackbackCount,@istop,@enableInRss)
            SELECT @logID=@@IDENTITY
            EXEC UpdateArticleCount
            @oldcatID=@catID,
            @catID=@catID,
            @authorID=@authorID");
            db.AddInParameter(dbCommand, "catID", DbType.Int32, item.CategoryID);
            db.AddInParameter(dbCommand, "title", DbType.String, item.Title);
            db.AddInParameter(dbCommand, "authorID", DbType.Int32, item.AuthorID);
            db.AddInParameter(dbCommand, "author", DbType.String, item.Author);
            db.AddInParameter(dbCommand, "editMark", DbType.String, item.EditMark);

            db.AddInParameter(dbCommand, "trackbackURL", DbType.String, item.TrackbackURL);
            db.AddInParameter(dbCommand, "content0", DbType.String, item.Content0);
            db.AddInParameter(dbCommand, "content1", DbType.String, item.Content1);
            db.AddInParameter(dbCommand, "mode", DbType.Int16, item.Mode);
            db.AddInParameter(dbCommand, "locked", DbType.Boolean, item.Locked);

            db.AddInParameter(dbCommand, "selected", DbType.Boolean, item.Selected);
            db.AddInParameter(dbCommand, "ubbFlags", DbType.String, item.UbbFlags);
            db.AddInParameter(dbCommand, "postTime", DbType.DateTime, item.PostTime);
            db.AddInParameter(dbCommand, "ip", DbType.String, item.IP);
            db.AddInParameter(dbCommand, "commentCount", DbType.Int32, item.CommentCount);
            db.AddInParameter(dbCommand, "viewCount", DbType.Int32, item.ViewCount);
            db.AddInParameter(dbCommand, "trackbackCount", DbType.Int32, item.TrackBackCount);
            db.AddInParameter(dbCommand, "istop", DbType.Boolean, item.IsTop);
            db.AddInParameter(dbCommand, "enableInRss", DbType.Boolean, item.EnableInRss);
            //db.AddInParameter(dbCommand, "enableComment", DbType.Boolean, item.EnableComment);
            db.AddOutParameter(dbCommand, "logID", DbType.Int32,10);
            db.ExecuteNonQuery(dbCommand);
            
            int result=Convert.ToInt32(db.GetParameterValue(dbCommand, "@logID"));
            item.ArticleID = result;
            return result;
        }

        public override void UpdateArticle(Article item)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"DECLARE @oldCatID int
            SET @oldCatID=(SELECT log_catID FROM blog_article WHERE log_id=@articleID)

            UPDATE [dbo].[blog_Article] 
            SET [log_catID] = @catID, [log_title] = @title, [log_authorID] = @authorID, [log_author] = @author, 
            [log_editMark] = @editMark, [log_trackbackURL] = @trackbackURL, [log_content0] = @content0, 
            [log_content1] = @content1, [log_mode] = @mode, [log_locked] = @locked, [log_selected] = @selected, 
            [log_ubbFlags] = @ubbFlags, [log_postTime] = @postTime, [log_ip] = @ip,log_istop=@istop,log_enableRss=@enableInRss
            WHERE (([log_id] = @articleID))

            EXEC UpdateArticleCount
            @oldCatID=@oldCatID,
            @catID=@catID,
            @authorID=@authorID");
            db.AddInParameter(dbCommand, "articleID", DbType.Int32, item.ArticleID);
            db.AddInParameter(dbCommand, "catID", DbType.Int32, item.CategoryID);
            db.AddInParameter(dbCommand, "title", DbType.String, item.Title);
            db.AddInParameter(dbCommand, "authorID", DbType.Int32, item.ArticleID);
            db.AddInParameter(dbCommand, "author", DbType.String, item.Author);
            db.AddInParameter(dbCommand, "editMark", DbType.String, item.EditMark);

            db.AddInParameter(dbCommand, "trackbackURL", DbType.String, item.TrackbackURL);
            db.AddInParameter(dbCommand, "content0", DbType.String, item.Content0);
            db.AddInParameter(dbCommand, "content1", DbType.String, item.Content1);
            db.AddInParameter(dbCommand, "mode", DbType.Int32, item.Mode);
            db.AddInParameter(dbCommand, "locked", DbType.Boolean, item.Locked);

            db.AddInParameter(dbCommand, "selected", DbType.Boolean, item.Selected);
            db.AddInParameter(dbCommand, "ubbFlags", DbType.String, item.UbbFlags);
            db.AddInParameter(dbCommand, "postTime", DbType.DateTime, item.PostTime);
            db.AddInParameter(dbCommand, "ip", DbType.String, item.IP);
            db.AddInParameter(dbCommand, "istop", DbType.Boolean, item.IsTop);
            db.AddInParameter(dbCommand, "enableInRss", DbType.Int32, item.EnableInRss);
            //db.AddInParameter(dbCommand, "enableComment", DbType.Int32, item.EnableComment);
            db.ExecuteNonQuery(dbCommand);
        }

        public override void UpdateArticleCount(int oldCatID, int catID, int authorID)
        {
            if (oldCatID != catID)
            {
                int oldcatCounter = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_catid={0}", oldCatID));
                ExecuteNonQuery(string.Format("UPDATE blog_Category SET cat_articleCount ={0}  WHERE cat_id={1}", oldcatCounter, oldCatID));
            }
            int catCounter = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_catid={0}", catID));
            int userCount = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_authorID={0}", authorID));
            int artCount = ExecuteScalar("select count(log_id) from blog_Article");
            ExecuteNonQuery(string.Format("UPDATE blog_Category SET cat_articleCount ={0}  WHERE cat_id={1}", catCounter, catID));
            ExecuteNonQuery(string.Format("UPDATE blog_User SET user_articleCount ={0}  WHERE user_id={1}", userCount, catID));
            ExecuteNonQuery(string.Format("UPDATE blog_Settings SET set_value0 ={0}  WHERE set_name='counterArticle'", artCount));
        }

        public override void UpdateArticleViewCount(int articleId)
        {
            ExecuteNonQuery(string.Format("update blog_article set log_viewcount=log_viewcount+1 where log_id={0}", articleId));
        }

        public override void DeleteArticle(int articleID)
        {
            DbCommand dbCommand = db.GetSqlStringCommand(@"DECLARE @artCounter int,@catID int,@userID int
            SELECT @catID=log_catid,@userID=log_authorID FROM blog_article WHERE log_id=@articleID
            DELETE FROM blog_comment where log_id=@articleID
            DELETE FROM blog_Trackback where log_id=@articleID
            DELETE FROM blog_Tag where log_ID=@articleID
            DELETE FROM [dbo].[blog_Article] WHERE (([log_id] = @articleID))


            EXEC UpdateArticleCount
                @oldCatID= @catID,
	            @catID=@catID,
	            @authorID=@userID");
            db.AddInParameter(dbCommand, "articleID", DbType.Int32, articleID);
            db.ExecuteNonQuery(dbCommand);
        }

        public override int GetArticleCategoryCount(int catID)
        {
            string strSql = "SELECT COUNT(log_id) FROM blog_Article";
            if (catID > 0)
            {
                strSql += " WHERE log_catID=@catID";
            }
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            db.AddInParameter(dbCommand, "catID", DbType.Int32, catID);
            return Convert.ToInt32(db.ExecuteScalar(dbCommand));
        }

        public override IList<Archive> GetArticleMonthArchive()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT Month(log_posttime) AS [Month]
	            , Year(log_posttime) AS [Year]
	            , 1 AS Day, Count(log_id) AS [Count] 
            FROM [dbo].[blog_article] 
            GROUP BY Year(log_posttime), Month(log_posttime) ORDER BY [Year] DESC, [Month] DESC");
            IList<Archive> lists = new List<Archive>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Archive archive = CommonDataProvider.PopulateArchiveFromIDataReader(dataReader);
                    lists.Add(archive);
                }
            }
            return lists;
        }

        public override IList<Article> GetAllArticleByMonth(int year, int month)
        {
            
            DbCommand dbCommand = db.GetStoredProcCommand(@"SELECT  *
            FROM blog_Article WHERE Year(log_postTime) = @year
	            AND Month(log_postTime) = @month");
            db.AddInParameter(dbCommand, "year", DbType.Int32, year);
            db.AddInParameter(dbCommand, "month", DbType.Int32, month);
            IList<Article> lists = new List<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateNoCatArticleFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        public override IList<ArticleLink> GetPreviousAndNextArticle(int articleID)
        {
            
            DbCommand dbCommand = db.GetStoredProcCommand("GetArticle_PreviousNext");
            db.AddInParameter(dbCommand, "articleID", DbType.Int32, articleID);
            IList<ArticleLink> lists = new List<ArticleLink>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ArticleLink article = CommonDataProvider.PopulateArticleLinkFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        #endregion


    }
}
